"""
Case Type   : VIEW
Case Name   : 参数track_stmt_stat_level设为L0,OFF，查询statement_history记录的for 用来执行游标的循环存储过程的慢sql
Create At   : 2024/10/9
Owner       : lonely-dance
Description :
    1.设置参数
    2.创建表
    3.创建存储过程
    4.调用存储过程
    5.查询系统视图pg_stat_activity
    6.清理环境
Expect      :
    1.设置参数成功
    2.创建表成功
    3.创建存储过程成功
    4.调用存储过程成功
    5.statement_history记录调用存储过程的慢sql
    6.清理环境成功
History     :
"""
import time
import unittest
import os
from testcase.utils.Logger import Logger
from testcase.utils.Constant import Constant
from testcase.utils.CommonSH import CommonSH
from testcase.utils.ComThread import ComThread
from yat.test import Node
from yat.test import macro


primary_sh = CommonSH('PrimaryDbUser')

@unittest.skipIf(3 != primary_sh.get_node_num(), '非1+2环境不执行')
class Analysis(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'---{os.path.basename(__file__)} start---')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.standby1_sh = CommonSH('Standby1DbUser')
        self.p_node = Node('PrimaryDbUser')
        self.s1_node = Node('Standby1DbUser')
        self.constant = Constant()
        self.database_name = 'test_track_stmt_stat_level'
        self.parameter1 = 'track_stmt_stat_level'
        self.parameter2 = 'log_min_duration_statement'
        self.primary_sh.execut_db_sql(f'create database {self.database_name};')

    def test_Analysis(self):
        text = '-----step1:设置参数 expect:成功-----'
        self.log.info(text)
        self.primary_sh.execute_gsguc('reload', self.constant.GSGUC_SUCCESS_MSG, "instr_unique_sql_count = 200000")
        res = self.primary_sh.execut_db_sql(f'show instr_unique_sql_count;')
        self.assertIn('200000', res, '执行失败:' + text)
        set_cmd = f'source {macro.DB_ENV_PATH};' \
                f'gsql -d {self.database_name} -p {self.p_node.db_port} -c ' \
                f'\"ALTER DATABASE {self.p_node.db_name} SET track_stmt_stat_level = \'L0,OFF\' ; ' \
                f'ALTER DATABASE {self.p_node.db_name} SET log_min_duration_statement = 1000 ;\";'
        set_res = self.p_node.sh(set_cmd).result()
        self.assertIn(self.constant.ALTER_DATABASE_SUCCESS_MSG, set_res, '执行失败:' + text)
        check_res1 = self.primary_sh.execut_db_sql(f'show {self.parameter1};')
        self.assertIn('L0,OFF', check_res1, '执行失败:' + text)
        check_res2 = self.primary_sh.execut_db_sql(f'show {self.parameter2};')
        self.assertIn('1s', check_res2, '执行失败:' + text)

        text = '-----step2:创建表数据 expect:成功-----'
        self.log.info(text)
        create_data = '''drop table if exists big_table;
            CREATE TABLE big_table (column1 INT, column2 VARCHAR(100)); 
            INSERT INTO big_table (column1, column2) SELECT generate_series(1, 10000000),
            'data' || generate_series(1, 10000000);'''
        self.log.info(create_data)
        result = self.primary_sh.execut_db_sql(create_data)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, result, '执行失败:' + text)
        self.assertIn('INSERT 0 10000000', result, '执行失败:' + text)

        text = '-----step3:创建存储过程 expect:成功-----'
        self.log.info(text)
        create_procedure = '''
            CREATE OR REPLACE PROCEDURE test_exec_fors0019() AS 
            DECLARE 
                data VARCHAR(100); 
            BEGIN 
                FOR data IN SELECT column2 FROM big_table LOOP 
                    RAISE NOTICE 'column_value: %', data; 
                END LOOP; 
            END; 
        '''
        self.log.info(create_procedure)
        result = self.primary_sh.execut_db_sql(create_procedure)
        self.log.info(result)
        self.assertEqual(self.constant.CREATE_PROCEDURE_SUCCESS_MSG, result, '执行失败:' + text)

        text = '-----step4:调用存储过程 expect:成功-----'
        self.log.info(text)
        sql_cmd = '''call test_exec_fors0019();'''
        sql_res = self.primary_sh.execut_db_sql(sql_cmd)
        self.assertIn('(1 row)', sql_res, '执行失败:' + text)

        text = '-----step5:statement_history expect:statement_history记录调用存储过程的慢sql-----'
        self.log.info(text)
        sql_cmd = f'''select * from statement_history where db_name = '{self.p_node.db_name}' order by finish_time desc;'''
        sql_result = self.primary_sh.execut_db_sql(sql_cmd, dbname='postgres')
        self.log.info(sql_result)
        self.assertIn('test_exec_fors0019()', sql_result,
                      '执行失败:' + text)

    def tearDown(self):
        text = '-----step6:清理环境 expect:成功-----'
        self.log.info(text)

        self.primary_sh.execute_gsguc('reload', self.constant.GSGUC_SUCCESS_MSG, "instr_unique_sql_count = 100")
        res = self.primary_sh.execut_db_sql(f'show instr_unique_sql_count;')
        self.assertIn('100', res, '执行失败:' + text)

        set_cmd = f'source {macro.DB_ENV_PATH};' \
                  f'gsql -d {self.database_name} -p {self.p_node.db_port} -c ' \
                  f'\"ALTER DATABASE {self.p_node.db_name} SET track_stmt_stat_level = \'OFF,L0\' ; ' \
                  f'ALTER DATABASE {self.p_node.db_name} SET log_min_duration_statement = 1800000 ;\";'
        set_res = self.p_node.sh(set_cmd).result()
        self.assertTrue(set_res.count(self.constant.ALTER_DATABASE_SUCCESS_MSG) == 2, '执行失败:' + text)
        check_res1 = self.primary_sh.execut_db_sql(f'show {self.parameter1};')
        self.assertIn('OFF,L0', check_res1, '执行失败:' + text)
        check_res2 = self.primary_sh.execut_db_sql(f'show {self.parameter2};')
        self.assertIn('30min', check_res2, '执行失败:' + text)

        sql_cmd = f'''
        drop database {self.database_name};
        drop table if exists big_table; 
        drop procedure if exists test_exec_fors0019; 
        '''
        self.log.info(sql_cmd)

        sql_result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_result)
        self.assertTrue(self.constant.DROP_PROCEDURE_SUCCESS_MSG in sql_result
                        and self.constant.DROP_DATABASE_SUCCESS in sql_result
                        and self.constant.DROP_TABLE_SUCCESS in sql_result, '执行失败:' + text)




